from flask import Flask, jsonify, send_file, request
from flask_cors import CORS
from sqlalchemy import create_engine, text
import pandas as pd
import io
import time
import logging


# 配置日志记录
logging.basicConfig(
    filename='app.log',  # 日志文件名
    level=logging.INFO,  # 日志级别
    format='%(asctime)s - %(levelname)s - %(message)s'  # 日志格式
)

# 示例：记录应用启动日志
logging.info("Application is starting...")


# 数据库连接配置
DB_CONFIG = {
    'host': 'rm-wz9wp4e9c4c266877eo.mysql.rds.aliyuncs.com',
    'port': '3306',
    'user': 'abd_data_ac',
    'password': 'ZYIXWu7Zjd',
    'database': 'abd_data'
}

# 创建数据库连接
engine = create_engine(
    f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@"
    f"{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
)



app = Flask(__name__)


# 添加CORS支持
CORS(app, resources={
    r"/api/*": {
        "origins": ["http://localhost:5173"],
        "methods": ["GET", "POST", "OPTIONS"],
        "allow_headers": ["Content-Type"]
    }
})


@app.route('/api/table/product_items', methods=['GET'])
def get_table_data():
    """获取数据"""
    try:
        logging.info("Fetching product items data...")
        # 分页查询
        query = text("""
            SELECT * FROM product_pool_items WHERE craw_date = (SELECT MAX(craw_date) FROM product_pool_items)
        """)

        df = pd.read_sql(
            query,
            engine,
        )

        df['indexUpdatedAt'] = pd.to_datetime(df['indexUpdatedAt'].astype(int), unit='s').dt.strftime('%Y-%m-%d')
        df['productUrl'] = df.apply(lambda row: f"https://www.amazon.{row['marketplace']}/dp/{row['asin']}", axis=1)
        df['age'] = pd.to_datetime(df['age'].astype(int), unit='s').dt.strftime('%Y-%m-%d')
        df['weight'] = df['weight'].apply(lambda x: f"{float(x):.2f}")
        df['price'] = df['price'].apply(lambda x: int(x)/100)
        df['reviewCount'] = df['reviewCount'].apply(lambda x: 0 if '-' in str(x) else int(x))
        df['reviewsRating'] = df['reviewsRating'].apply(lambda x: '-' if '-' in str(x) else x)
        data = df.to_dict('records')

        logging.info("Product items data fetched successfully.")
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': data,
            'totalRecords': len(data)

        })

    except Exception as e:
        logging.info("Product items data fetched fail.")
        return jsonify({
            'code': 500,
            'message': f'获取失败: {str(e)}',
            'data': None
        })


@app.route('/api/table/download', methods=['GET'])
def download_table_data():
    """导出全部数据为CSV"""
    try:
        logging.info("Download table ...")
        # 分批次读取数据
        chunks = []
        chunk_size = 10000

        for chunk_df in pd.read_sql(
                "SELECT * FROM product_pool_items WHERE craw_date = (SELECT MAX(craw_date) FROM product_pool_items)",
                engine,
                chunksize=chunk_size
        ):
            chunks.append(chunk_df)

        # 合并数据
        df = pd.concat(chunks)

        # 写入内存
        output = io.BytesIO()
        df.to_csv(output, index=False)
        output.seek(0)

        # 返回文件
        logging.info("Download table successfully.")
        return send_file(
            output,
            mimetype='text/csv',
            as_attachment=True,
            download_name=f'product_items_{int(time.time())}.csv'
        )

    except Exception as e:
        logging.info("Download table fail.")
        return jsonify({
            'code': 500,
            'message': f'导出失败: {str(e)}'
        })





@app.route('/api/table/get_categories', methods=['GET'])
def get_categories():
    """分页获取数据"""
    try:
        logging.info("Fetching categories items data...")
        page = request.args.get('page', 1, type=int)
        per_page = request.args.get('per_page', 1000, type=int)
        offset = (page - 1) * per_page

        # 分页查询
        query = text("""
            SELECT DISTINCT(`categoryTitle`) FROM `product_pool_items`
        """)

        df = pd.read_sql(
            query,
            engine
        )

        # 将数据转换为简单列表
        categories = df['categoryTitle'].tolist()

        logging.info("Getting categories successfully.")
        return jsonify({
            'code': 200,
            'message': '获取成功',
            'data': categories
        })

    except Exception as e:
        logging.info("Getting categories fail.")
        return jsonify({
            'code': 500,
            'message': f'获取失败: {str(e)}',
            'data': None
        })




@app.route('/api/table/get_total_num', methods=['GET'])
def count_marketplace():
    """计算 marketplace 为 'it' 的数据行数"""
    try:
        logging.info('Counting total records for all marketplace ...')
        query = text("""
            SELECT COUNT(*) as count
            FROM product_pool_items
            WHERE marketplace = :marketplace
        """)

        # 创建数据库连接
        with engine.connect() as connection:
            result = connection.execute(query, {'marketplace': 'it'}).fetchone()
            count = result[0]

        logging.info(f'Counting total records {count} successfully.')
        return jsonify({
            'code': 200,
            'message': '计算成功',
            'totalRecords': count,
        })

    except Exception as e:
        logging.info(f'Counting total records fail.')
        return jsonify({
            'code': 500,
            'message': f'计算失败: {str(e)}',
            'totalRecords': None
        })







if __name__ == '__main__':
    app.run(
        host='0.0.0.0',
        port=5000,
        debug=False
    )






